"""
Case Type   : 功能测试
Case Name   : 使用timestampdiff函数计算两个日期时间之间的差值
Create At   : 2020/8/2
Owner       : @li-xin12345
Description : TIMESTAMPDIFF(unit , timestamp_expr1, timestamp_expr2)
    timestampdiff函数是计算两个日期时间之间(timestamp_expr2-timestamp_expr1)的差值，并以unit形式返回结果。
    timestamp_expr1，timestamp_expr2必须是一个timestamp、timestamptz、date类型的值表达式。unit表示的是两个日期差的单位。
    该函数仅在openGauss兼容MY类型时（即dbcompatibility = 'B'）有效，其他类型不支持该函数。
    1.创建兼容MY的数据库
    2.在该数据库中执行timestampdiff函数
Expect      : 
    1.创建成功
    2.函数返回结果正确
History     : 
"""

import os
import unittest

from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant


class Function(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.commonsh = CommonSH('dbuser')
        self.db_name = 'db_timestampdiff_0001'
        self.constant = Constant()

    def test_timestampdiff(self):
        sql_cmd = ["select timestampdiff(year, '2018-01-01', '2020-01-01');",
                   "select timestampdiff(quarter, '2018-01-01', '2020-01-01');",
                   "select timestampdiff(month, '2018-01-01', '2020-01-01');",
                   "select timestampdiff(week, '2018-01-01', '2020-01-01');",
                   "select timestampdiff(day, '2018-01-01', '2020-01-01');",
                   "select timestampdiff(hour, '2020-01-01 10:10:10', "
                   "'2020-01-01 11:11:11');",
                   "select timestampdiff(minute, '2020-01-01 10:10:10', "
                   "'2020-01-01 11:11:11');",
                   "select timestampdiff(second, '2020-01-01 10:10:10', "
                   "'2020-01-01 11:11:11');",
                   "select timestampdiff(microsecond, "
                   "'2020-01-01 10:10:10.000000', "
                   "'2020-01-01 10:10:10.111111');",
                   "select timestampdiff(hour,'2020-05-01 10:10:10-01',"
                   "'2020-05-01 10:10:10-03');"]
        expect = ['2', '8', '24', '104', '730', '1', '61', '3661',
                  '111111', '0']
        warning_msg = 'WARNING:  Truncated incorrect date or datetime value'
        text = '------step1:建B库-----'
        self.log.info(text)
        db_create = f"drop database if exists {self.db_name};" \
                    f"create database {self.db_name} dbcompatibility 'B';"
        msg1 = self.commonsh.execut_db_sql(db_create)
        self.log.info(msg1)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, msg1,
                      '执行失败' + text)

        text = '-----step2:执行不同场景timestampdiff语法------'
        self.log.info(text)
        for j in range(len(sql_cmd)):
            result = self.commonsh.execut_db_sql(sql_cmd[j],
                                                 dbname=self.db_name)
            self.log.info(result)
            if j < 9:
                self.assertTrue(result.splitlines()[-2].strip() == expect[j],
                                '执行失败' + text)
            if j == 9:
                self.assertIn(warning_msg, result, '执行失败' + text)

    def tearDown(self):
        text = '-----清理环境-----'
        self.log.info(text)
        sql = f'drop database if exists {self.db_name};'
        res = self.commonsh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, res,
                      '执行失败' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')